use crate::{
    helpers::{
        count_ttr, create_ttr, redirect_frontends,
        /*count_ttr, create_ttr,*/ /*link_finder/**/ , */ remove_tracking_parameters,
        wrap_url, DatabaseConnection,
    },
    models::{Message, Post, UserPost},
};
use urlexpand::unshorten;
//  use ActiveValue::NotSet;
use anyhow::{Context, Result};
use log::info;
use matrix_sdk::ruma::events::room::message::RoomMessageEventContent;

use matrix_sdk::{room::Joined, ruma::events::room::message::OriginalSyncRoomMessageEvent, Client};
use serde::{Deserialize, Serialize};
use url::Url;

/// Add new url to the db and belong it to user
pub async fn add(
    ev: OriginalSyncRoomMessageEvent,
    text: String,
    room: Joined,
    _client: Client,
    db: DatabaseConnection,
) -> Result<()> {
    // let db = connect_to_db().await?;
    tokio::spawn(async move {
        let user_id = ev.sender.as_str();
        println!("{user_id:#?}");
        if let Ok(mut url) = Url::parse(&text) {
            remove_tracking_parameters(&mut url);
            let real_url = get_real_url(&url).await;

            let user_id = {
                if let Some(user_id) = user_exists(user_id, &db).await.unwrap() {
                    user_id
                } else {
                    create_user(user_id, &db).await.unwrap()
                }
            };
            let post = {
                if let Some(post) = post_exists(&real_url, &db).await.unwrap() {
                    post
                } else {
                    let post = create_post(&real_url).await.unwrap();
                    save_post_to_db(&post, &db).await.unwrap();
                    post
                }
            };
            let userpost_id = {
                if let Some(userpost_id) = check_duplicates_in_post_map(user_id, post.id(), &db)
                    .await
                    .unwrap()
                {
                    userpost_id
                } else {
                    append_post_to_user(user_id, post.id(), &db, &url)
                        .await
                        .unwrap()
                }
            };
            // let content = RoomMessageEventContent::text_plain(url.to_string());

            // println!("sending");

            // send our message to the room we found the "!party" command in
            // the last parameter is an optional transaction id which we don't
            // care about.
            // room.send(content, None).await.unwrap();

            // println!("message sent");
            // bot.send_message(telegram_user.id, url.to_string())
            //     .reply_markup(keyboards::standart_keyboard(
            //         &post_id.to_hyphenated().to_string(),
            //         user_post_info.read,
            //         user_post_info.favorite,
            //     ))
            //     .await?;
            // If post does not exist, create it

            // let event_id = ev.event_id.to_string();
            log::info!("Successful saved post");
            // create_message_event(userpost_id, &db, event_id).await.unwrap();
            let mut answer = {
                let url_str = url.as_str();
                if let Some(title) = post.title() {
                    wrap_url(url_str, &title)
                } else {
                    url_str.to_string()
                }
            };
            let ttr = count_ttr(post.word_count);
            if let Some(ttr) = ttr {
                create_ttr(&mut answer, ttr)
            };

            let content = RoomMessageEventContent::text_markdown(answer);

            println!("sending");

            // send our message to the room we found the "!party" command in
            // the last parameter is an optional transaction id which we don't
            // care about.
            let response = room.send(content, None).await.unwrap();
            let event_id = response.event_id.to_string();
            create_message_event(userpost_id, &db, event_id)
                .await
                .unwrap();
            dbg!(response);

            println!("message sent");
        };
    });

    //println!("{:#?}", ev);
    //  let urls = link_finder(&text);
    // if urls.is_empty() {
    //     info!("No urls in the message!");
    // }
    // info!("Urls in the message: {:#?}", urls);
    // let db = connect_to_db()
    //     .await
    //     .context("Error while connecting to db:")?;
    // let telegram_user = m.from().context("Error while get telegram user data:")?;
    // let user_id = telegram_user.id;
    // let user_username = telegram_user.username.clone();
    // };
    // for mut url in urls {
    //     remove_tracking_parameters(&mut url);
    //     let mut real_url = real_url(&url).await;
    //     remove_tracking_parameters(&mut real_url);
    //     // If post exists, see if it belongs to the user.
    //     if let Ok(post_id) = post_exists(&real_url, &db).await {
    //         if let Some(user_post_info) =
    //             check_duplicates_in_post_map(user_id, post_id, &db, &url).await?
    //         {
    //             bot.send_message(telegram_user.id, url.to_string())
    //                 .reply_markup(keyboards::standart_keyboard(
    //                     &post_id.to_hyphenated().to_string(),
    //                     user_post_info.read,
    //                     user_post_info.favorite,
    //                 ))
    //                 .await?;
    //         }
    //         // If post does not exist, create it
    //     } else {
    //         let post = create_post(real_url).await?;
    //         save_post_to_db(&post, &db).await?;
    //         append_post_to_user(user_id, post.id(), &db, url.as_str()).await?;
    //         let _post_id = post.id();

    //         log::info!("Successful saved post");
    //         let mut answer = {
    //             let url_str = url.as_str();
    //             if let Some(title) = post.title() {
    //                 wrap_url(url_str, &title)
    //             } else {
    //                 escape(&escape_link_url(url_str))
    //             }
    //         };
    //         let ttr = count_ttr(post.word_count);
    //         if let Some(ttr) = ttr {
    //             create_ttr(&mut answer, ttr)
    //         };
    //         bot.send_message(telegram_user.id, answer)
    //             .reply_markup(keyboards::standart_keyboard(
    //                 &post.id().to_hyphenated().to_string(),
    //                 false,
    //                 false,
    //             ))
    //             //   .parse_mode(ParseMode::MarkdownV2)
    //             .await?;
    //     }
    // }

    Ok(())
}
// use url::Url;
// // Open web page using reqwest and get final url
use reqwest::Client as ReqwestClient;
use std::{ops::Deref, time::Duration};
async fn get_real_url(original_url: &Url) -> Url {
    let proxy = std::env::var("PROXY").unwrap();
    let timeout = Duration::from_secs(10);
    if let Ok(url) = unshorten(original_url.as_str(), Some(timeout)).await {
        let url = Url::parse(&url).unwrap();
        info!("{url}");
        url
    } else if let Ok(res) = ReqwestClient::builder()
        .proxy(reqwest::Proxy::https(proxy).unwrap())
        .build()
        .unwrap()
        .get(original_url.to_owned())
        .timeout(timeout)
        .send()
        .await
    {
        //TODO: work with url
        let real_url = res.url().to_owned();
        info!("{}", real_url);
        real_url
    } else {
        // info!("{:#?}", e);
        original_url.to_owned()
    }
}
use time::OffsetDateTime;
use uuid::Uuid;
fn created() -> OffsetDateTime {
    OffsetDateTime::now_utc()
}
async fn post_exists(post_url: &Url, db: &DatabaseConnection) -> Result<Option<Post>> {
    let rows = db
        .lock()
        .await
        .deref()
        .query(
            "SELECT id, real_url, created, title, word_count, alternative_url  FROM posts WHERE real_url=$1",
            &[&post_url.as_str()],
        )
        .await?;
    if let Some(row) = rows.get(0) {
        // .get(0).context("post_query_error")?
        let post = Post::builder()
            .id(row.get(0))
            .real_url(row.get(1))
            .created(row.get(2))
            .title(row.get(3))
            .word_count(row.get(4))
            .alternative_url(row.get(5))
            .build();
        Ok(Some(post))
    } else {
        Ok(None)
    }
}
// TODO: refactor this
async fn check_duplicates_in_post_map(
    user_id: Uuid,
    post_id: Uuid,
    db: &DatabaseConnection,
) -> Result<Option<Uuid>> {
    let rows = db
        .lock()
        .await
        .deref()
        .query(
            "SELECT (id) FROM userposts WHERE (user_id = $1 AND post_id = $2)",
            &[&user_id, &post_id],
        )
        .await
        .context("usermap query error")?;
    // .get(0);
    if let Some(row) = rows.get(0) {
        dbg!(row);
        // unimplemented!();
        // Ok(Some(UserPostInfo{
        // read: row.get(0),
        //     favorite: row.get(1),
        // }))
        let id = row.get(0);
        Ok(Some(id))
    } else {
        Ok(None)
    }
}
#[derive(Debug, Serialize, Deserialize)]
pub struct UserPostInfo {
    read: bool,
    favorite: bool,
}
async fn append_post_to_user(
    user_id: Uuid,
    post_id: Uuid,
    db: &DatabaseConnection,
    original_url: &Url,
) -> Result<Uuid> {
    let user_post = UserPost::builder()
        .id(Uuid::new_v4())
        .user_id(user_id)
        .original_url(original_url.to_string())
        .favorite(false)
        .post_id(post_id)
        .read(false)
        .show_real_url(false)
        .read_at(None)
        .show_alternative_url(false)
        .build();
    db
    .lock()
    .await
    .deref()
    .query("INSERT INTO userposts (id, user_id, original_url, favorite, post_id, read, show_real_url, read_at) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)", &[&user_post.id(), &user_post.user_id(), &user_post.original_url(), &user_post.favorite(), &user_post.post_id(), &user_post.read(), &user_post.show_real_url(), &user_post.read_at()]).await?;
    Ok(user_post.id())
}
async fn save_post_to_db(post: &Post, db: &DatabaseConnection) -> Result<()> {
    db
    .lock()
    .await
    .deref()
    .query(
        "INSERT INTO posts (id, title, word_count, created, real_url, alternative_url) VALUES ($1, $2, $3, $4, $5, $6)",
        &[
            &post.id(),
            &post.title(),
            &post.word_count(),
            &post.created(),
            &post.real_url(),
            &post.alternative_url(),
        ],
    )
    .await?;
    Ok(())
}
async fn get_post_id(real_url: &Url, db: &DatabaseConnection) -> Result<Uuid> {
    let res = db
        .lock()
        .await
        .deref()
        .query_one(
            "SELECT id FROM posts WHERE real_url = $1",
            &[&real_url.to_string()],
        )
        .await?
        .get(0);
    Ok(res)
}
async fn create_post(real_url: &Url) -> Result<Post> {
    let created = created();
    let alternative_url = redirect_frontends(real_url);
    let id = uuid::Uuid::new_v4();
    // let mut title = None;
    // let mut word_count = None;
    let parser_output = parser::parse(real_url).await?;
    let post = Post::builder()
        .id(id)
        .created(created)
        .real_url(real_url.to_string())
        .title(parser_output.title)
        .word_count(parser_output.word_count)
        .alternative_url(alternative_url.map(|s| s.to_string()))
        .build();
    Ok(post)
}

async fn user_exists(user_id: &str, db: &DatabaseConnection) -> Result<Option<Uuid>> {
    let row = db
        .lock()
        .await
        .deref()
        .query("SELECT id FROM users WHERE matrix_id = $1", &[&user_id])
        .await?;
    if !row.is_empty() {
        Ok(Some(row.get(0).context("user query error")?.get(0)))
    } else {
        Ok(None)
    }
}

pub async fn create_user(matrix_id: &str, db: &DatabaseConnection) -> Result<Uuid> {
    let joined = OffsetDateTime::now_utc();
    let show_real_url = false;
    let id = uuid::Uuid::new_v4();
    db.lock()
        .await
        .deref()
        .query(
            "insert into users (id, matrix_id, joined, show_real_url) values ($1, $2, $3, $4)",
            &[&id, &matrix_id.to_string(), &joined, &show_real_url],
        )
        .await?;
    Ok(id)
}
pub async fn create_message_event(
    userpost_id: Uuid,
    db: &DatabaseConnection,
    event_id: String,
) -> Result<()> {
    let message = Message::builder()
        .userpost_id(userpost_id)
        .event_id(event_id)
        .sent(OffsetDateTime::now_utc())
        .build();
    db.lock()
        .await
        .deref()
        .query(
            "INSERT INTO messages (userpost_id, event_id, sent) VALUES ($1, $2, $3)",
            &[&message.userpost_id(), &message.event_id(), &message.sent()],
        )
        .await?;
    Ok(())
}
